package com.servicesDirectory.dao.organisation.department;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.organisation.DepartmentVO;
import com.servicesDirectory.dao.BaseDAO;

public class DepartmentDAOImpl implements DepartmentDAO, BaseDAO {
	Connection conn = null;

	// connection to database method
	public void initConnection() {
		if (conn == null) {
			try {
				Class.forName(JDBC_DRIVER);
				conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);

			} catch (Exception e) {
				e.getStackTrace();
			}
		} else {
			conn = null;
			try {
				Class.forName(JDBC_DRIVER);
				conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);

			} catch (Exception e) {
				e.getStackTrace();
			}
		}
	}

	@Override
	public boolean addDepartment(DepartmentVO vo) throws FunctionalException,
			SystemException {
		PreparedStatement stm = null;
		boolean checkAdd = false;
		initConnection();

		try {
			int intIsActive;
			if (vo.isActive()) {
				intIsActive = 1;
			} else {
				intIsActive = 0;
			}
			String qr = "INSERT INTO Department (DepartmentName,[ShortDesc],[ContactID],[CopyAddr],[Addr1],[Addr2],[Addr3],[Postcode],[TownName],[CountyName],[CountryName],[TypeOfBusinessID],[FullDesc],[PhoneNumber],[Fax],[Email],[WebAddr],[DirectorateID],[IsActive]) VALUES "
					+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			// input to query
			stm = conn.prepareStatement(qr);
			stm.setString(1, vo.getDepartmentName());
			stm.setString(2, vo.getShortDesc());
			stm.setInt(3, vo.getContactID()); // contactID
			stm.setInt(4, 1); // copyAddress
			stm.setString(5, vo.getAddr1());
			stm.setString(6, vo.getAddr2());
			stm.setString(7, vo.getAddr3());
			stm.setString(8, vo.getPostcode());
			stm.setString(9, vo.getTownName());
			stm.setString(10, vo.getCountyName());
			stm.setString(11, vo.getCountryName());
			stm.setInt(12, 1); // typeOfBusiness
			stm.setString(13, vo.getFullDesc());
			stm.setString(14, vo.getPhoneNumber());
			stm.setString(15, vo.getFax());
			stm.setString(16, vo.getEmail());
			stm.setString(17, vo.getWebAddr());
			stm.setInt(18, 5);
			stm.setInt(19, 1);

			// stm = conn.prepareStatement(qr);
			// stm.setString(1, vo.getDepartmentName());
			// stm.setString(2, vo.getShortDesc());
			// stm.setInt(3, 1); //contactID
			// stm.setInt(4,1); //copyAddress
			// stm.setString(5, vo.getAddr1());
			// stm.setString(6, vo.getAddr2());
			// stm.setString(7, vo.getAddr3());
			// stm.setString(8, vo.getPostcode());
			// stm.setString(9, vo.getTownName());
			// stm.setString(10, "County 44");
			// stm.setString(11, "Country 44");
			// stm.setInt(12, 1); //typeOfBusiness
			// stm.setString(13, "Full Des 44");
			// stm.setString(14, "Phone 44");
			// stm.setString(15, "Fax 44");
			// stm.setString(16, "Email 44");
			// stm.setString(17, "Web 44");
			// stm.setInt(18, 3);
			// stm.setInt(19, 1);

			stm.executeUpdate();
			checkAdd = true;
			return checkAdd;
		} catch (Exception e) {
			checkAdd = false;

			e.printStackTrace();
			return checkAdd;
		}
	}

	@Override
	public boolean amendDepartment(DepartmentVO vo) throws FunctionalException,
			SystemException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public List<DepartmentVO> listDepartment(int directorateID, String filter,
			boolean isInclude) throws FunctionalException, SystemException {
		initConnection();
		List<DepartmentVO> departments = null;
		DepartmentVO vo = null;
		ResultSet rs = null;
		String qr = "";
		Statement stm = null;
		departments = new LinkedList<DepartmentVO>();
		try {
			if (isInclude) {
				if (filter.equals("ABCDE")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND ( " + "DepartmentName LIKE 'A%' OR "
							+ "DepartmentName LIKE 'B%' OR "
							+ "DepartmentName LIKE 'C%' OR "
							+ "DepartmentName LIKE 'D%' OR "
							+ "DepartmentName LIKE 'E%'" + " )";
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND ( " + "DepartmentName LIKE 'F%' OR "
							+ "DepartmentName LIKE 'G%' OR "
							+ "DepartmentName LIKE 'H%' OR "
							+ "DepartmentName LIKE 'I%' OR "
							+ "DepartmentName LIKE 'J%'" + " )";
				} else if (filter.equals("KLMN")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND ( " + "DepartmentName LIKE 'K%' OR "
							+ "DepartmentName LIKE 'L%' OR "
							+ "DepartmentName LIKE 'M%' OR "
							+ "DepartmentName LIKE 'N%'" + " )";
				} else if (filter.equals("OPQR")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND ( " + "DepartmentName LIKE 'O%' OR "
							+ "DepartmentName LIKE 'P%' OR "
							+ "DepartmentName LIKE 'Q%' OR "
							+ "DepartmentName LIKE 'R%'" + " )";
				} else if (filter.equals("STUV")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND ( " + "DepartmentName LIKE 'S%' OR "
							+ "DepartmentName LIKE 'T%' OR "
							+ "DepartmentName LIKE 'U%' OR "
							+ "DepartmentName LIKE 'V%'" + " )";
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND ( " + "DepartmentName LIKE 'W%' OR "
							+ "DepartmentName LIKE 'X%' OR "
							+ "DepartmentName LIKE 'Y%' OR "
							+ "DepartmentName LIKE 'Z%'" + " )";
				} else if (filter.equals("0-9")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND ( " + "DepartmentName LIKE '0%' OR "
							+ "DepartmentName LIKE '1%' OR "
							+ "DepartmentName LIKE '2%' OR "
							+ "DepartmentName LIKE '3%' OR "
							+ "DepartmentName LIKE '4%' OR "
							+ "DepartmentName LIKE '5%' OR "
							+ "DepartmentName LIKE '6%' OR "
							+ "DepartmentName LIKE '7%' OR "
							+ "DepartmentName LIKE '8%' OR "
							+ "DepartmentName LIKE '9%'" + " )";
				} else {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID;
				}
			} else {

				if (filter.equals("ABCDE")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 " + "AND ( "
							+ "DepartmentName LIKE 'A%' OR "
							+ "DepartmentName LIKE 'B%' OR "
							+ "DepartmentName LIKE 'C%' OR "
							+ "DepartmentName LIKE 'D%' OR "
							+ "DepartmentName LIKE 'E%'" + " )";
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 " + " AND ( "
							+ "DepartmentName LIKE 'F%' OR "
							+ "DepartmentName LIKE 'G%' OR "
							+ "DepartmentName LIKE 'H%' OR "
							+ "DepartmentName LIKE 'I%' OR "
							+ "DepartmentName LIKE 'J%'" + " )";
				} else if (filter.equals("KLMN")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 " + " AND ( "
							+ "DepartmentName LIKE 'K%' OR "
							+ "DepartmentName LIKE 'L%' OR "
							+ "DepartmentName LIKE 'M%' OR "
							+ "DepartmentName LIKE 'N%'" + " )";
				} else if (filter.equals("OPQR")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 " + " AND ( "
							+ "DepartmentName LIKE 'O%' OR "
							+ "DepartmentName LIKE 'P%' OR "
							+ "DepartmentName LIKE 'Q%' OR "
							+ "DepartmentName LIKE 'R%'" + " )";
				} else if (filter.equals("STUV")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 " + " AND ( "
							+ "DepartmentName LIKE 'S%' OR "
							+ "DepartmentName LIKE 'T%' OR "
							+ "DepartmentName LIKE 'U%' OR "
							+ "DepartmentName LIKE 'V%'" + " )";
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 " + " AND ( "
							+ "DepartmentName LIKE 'W%' OR "
							+ "DepartmentName LIKE 'X%' OR "
							+ "DepartmentName LIKE 'Y%' OR "
							+ "DepartmentName LIKE 'Z%'" + " )";
				} else if (filter.equals("0-9")) {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 " + " AND ( "
							+ "DepartmentName LIKE '0%' OR "
							+ "DepartmentName LIKE '1%' OR "
							+ "DepartmentName LIKE '2%' OR "
							+ "DepartmentName LIKE '3%' OR "
							+ "DepartmentName LIKE '4%' OR "
							+ "DepartmentName LIKE '5%' OR "
							+ "DepartmentName LIKE '6%' OR "
							+ "DepartmentName LIKE '7%' OR "
							+ "DepartmentName LIKE '8%' OR "
							+ "DepartmentName LIKE '9%'" + " )";
				} else {
					qr = "SELECT " + "*, "
							+ "firstName + ' ' + surName as Contact, "
							+ "CASE Department.IsActive "
							+ "WHEN 1 THEN 'Yes' "
							+ "ELSE 'No'END AS 'Is Active' "
							+ "FROM Department INNER JOIN Contact "
							+ "ON Department.ContactID = Contact.ContactID "
							+ "WHERE DirectorateID = " + directorateID
							+ " AND Department.IsActive = 1 ";
				}
			}

			stm = conn.createStatement();
			rs = stm.executeQuery(qr);

			while (rs.next()) {

				vo = new DepartmentVO();
				vo.setDepartmentID(rs.getInt(1));
				vo.setDepartmentName(rs.getString(2));
				vo.setShortDesc(rs.getString(3));
				vo.setContactID(rs.getInt(4));
				vo.setCopyAddr(rs.getInt(5));
				vo.setAddr1(rs.getString(6));
				vo.setAddr2(rs.getString(7));
				vo.setAddr3(rs.getString(8));
				vo.setPostcode(rs.getString(9));
				vo.setTownName(rs.getString(10));
				vo.setCountyName(rs.getString(11));
				vo.setCountryName(rs.getString(12));
				vo.setTypeOfBusinessID(rs.getInt(13));
				vo.setFullDesc(rs.getString(14));
				vo.setPhoneNumber(rs.getString(15));
				vo.setFax(rs.getString(16));
				vo.setEmail(rs.getString(17));
				vo.setWebAddr(rs.getString(18));
				vo.setDirectorateID(rs.getInt(19));
				boolean active;
				if (rs.getInt(20) == 1) {
					active = true;
				} else {
					active = false;
				}
				vo.setActive(active);
				vo.setFullName(rs.getString(35));

				if (active == true) {
					vo.setStringIsActive("Yes");
				} else {
					vo.setStringIsActive("No");
				}

				
				departments.add(vo);

			}

		} catch (Exception e) {
			e.printStackTrace();
			return departments;
		}

		return departments;
	}

}
